/*
author:田伟后 闵亚楠 薛若梅 钱波
last modify: 钱波
*/
"use strict";
var util = require('util');
var Sqldb = require('./db_mysql.js').sqldb;

function dateFormat(date, fmt) {
    if (null == date || undefined == date) return '';
    var o = {
        "M+": date.getMonth() + 1, //月份
        "d+": date.getDate(), //日
        "h+": date.getHours(), //小时
        "m+": date.getMinutes(), //分
        "s+": date.getSeconds(), //秒
        "S": date.getMilliseconds() //毫秒
    };
    if (/(y+)/.test(fmt)) fmt = fmt.replace(RegExp.$1, (date.getFullYear() + "").substr(4 - RegExp.$1.length));
    for (var k in o)
        if (new RegExp("(" + k + ")").test(fmt)) fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
    return fmt;
}

Date.prototype.toJSON = function () { return dateFormat(this, 'yyyy-MM-dd hh:mm:ss') }

//var test_i = 0;
//console.log("now the test_i is %d", test_i++);
/*
接口
钱波 修改， 每个接口的tablename 现在修改到第一个参数
*/
function page_return(currentPage, icount, itemsPerPage) {
    if (icount <= 0)
        return null;
    var tcount = parseInt((icount - 1) / itemsPerPage + 1);
    if (currentPage > tcount)
        return null;
    var retobj = {};
    retobj.page = currentPage;
    retobj.records = icount;
    retobj.pagecount = tcount;
    return retobj;
}
function callback_timeout(key, value) {
    console.log(key + " time out");
}
var OUT_TIME_CONFIG = 5000 * 10; //设置50秒过期时间

class Db_modules {
    constructor() {
    }
    /**
     * 检查query_mode
     */
    check_query_mode(obj) {
        for (var key in obj.query_mode) {
            if (key == 'equal') {
                for (var key1 in obj.query_mode[key]) {
                    obj.query_mode[key][key1] += '=';
                }
            }
        }
        if (obj.query == null) {
            obj.query = Object.assign(obj.query_mode['like'], obj.query_mode['equal']);
            delete obj.query_mode;
        } else {
            obj.query = Object.assign(obj.query, obj.query_mode['like'], obj.query_mode['equal']);
            delete obj.query_mode;
        }
    }
    /*得到数据
    obj 是一个对象，包含
    {
        fields: "f1,f2", //选择的字段
        tablename,  //选择的表名称
        page {page, rows or limit, sidx,sord }, 
        where_in f_in  是否有字段in，没有为null    //f_in_array  字段in数组
        query:  "equal 和 like 查询"
        query_mode: {
            like:{},
            equal:{}
        }
        extra_where : "where field is not null"
        distinct:true or false
        cache:true or false ,如果为true ，则使用cache，否则从数据库中获取
        single:true or false
    }
    */
    db_get(obj, cb) { //或者是所有，或者是page
        if (cb == null || obj == null)
            return;
        var mysqldb = new Sqldb();
        // obj的query_mode处理
        if (obj.query_mode != null) {
            this.check_query_mode(obj);
        }
        var sql = mysqldb.querysql(false, obj);  //得到所有的sql
         // console.log(sql);
        if (sql == null)
            return cb(-1, "tablename or other is null");
        var cache = (obj.cache != null && obj.cache == true);
        if (cache) {
            var cache_ret = mysqlcache.get(sql);
            if (cache_ret != null) {
                console.log("hit the cache,return now");
                return cb(1, cache_ret);
            }
        }

        //page 第几页  limit rows 代表每页条目数
        if (obj.page != null) { //分页处理
            mysqldb.page_count_result(function (status, result) {//单次回调两个sql
                if (status < 0)
                    return cb(-1, null);
                var icount = result[0][0]['COUNT'];
                var rows = obj.page.rows != null ? obj.page.rows : obj.page.limit;
                var retobj = page_return(obj.page.page, icount, rows);
                if (retobj != null) {
                    retobj.rows = result[1];
                }
                if (cache)
                    mysqlcache.put(sql, retobj, OUT_TIME_CONFIG, callback_timeout);
                return cb(1, retobj);

            });
        }
        else { //没有分页处理， 返回所有数据
            if (obj.single == true) {//返回单条数据
                mysqldb.row(function (status, res) {
                    if (cache)
                        mysqlcache.put(sql, res, OUT_TIME_CONFIG, callback_timeout);
                    cb(status, res);
                })
            }
            else {
                mysqldb.result(function (status, res) {//返回所有数据
                    if (cache)
                        mysqlcache.put(sql, res, OUT_TIME_CONFIG, callback_timeout);
                    cb(status, res);
                });
            }
        }
    }


    //该函数增加了hash，如何不想使用cache，cache = false;
    //该函数不支持分页，单个对象不能有分页参数
    //objs:[{
    //fields:"," 逗号分隔字段
    //tablename:tablename,
    //query:query  (like 和 ==)
    //extra_where: where field is not null
    //distinct:distinct true or false
    //cache true or false 并不起作用
    //},{}]
    //cache :是否缓存
    db_gets(objs, cache, cb) {
        if (cb == null || objs == null)
            return;
        var mysqldb = new Sqldb();
        var sqls = "";
        for (var i = 0; i < objs.length; i++) {
            var obj = objs[i];
            var sql = mysqldb.querysql(true, obj);
            sqls += sql + ";";
        }
        if (cache) {
            var cache_ret = mysqlcache.get(sqls);
            if (cache_ret != null)
                return cb(1, cache_ret);
        }
        //存入结果
        //console.log("db_gets sqls:", sqls);
        mysqldb.querys(sqls, function (status, ret) {
            if (status >= 0 && cache) {
                mysqlcache.put(sql, ret, OUT_TIME_CONFIG, callback_timeout);
            }
            cb(status, ret);
        });
    }

    //2018.7.20 薛若梅 增加根据sql语句查询一条数据
    db_limit(sqls, cb) {
        var mysqldb = new Sqldb();
        mysqldb.querys(sqls, function (status, ret) {
            cb(status, ret);
        });
    }

    db_count(obj, cb) {
        if (obj == null || obj.tablename == null || cb == null) {
            console.log("db_info_insert data or tablename or cb is null");
            return;
        }

        var mysqldb = new Sqldb();
        var sql = mysqldb.querysql(false, obj);
        if (sql == null) {
            return cb(-1, "error obj cant not get sql");
        }
        var cache = (obj.cache != null && obj.cache == true);
        if (cache) {
            var cache_ret = mysqlcache.get(sqls);
            if (cache_ret != null)
                return cb(1, cache_ret);
        }
        mysqldb.count(function (status, ret) {
            if (status >= 0 && cache) {
                mysqlcache.put(sql, ret, OUT_TIME_CONFIG, callback_timeout);
            }
            cb(1, ret);
        });
    }

    //datas = [
    //{}
    //{}
    //]
    db_insert_plus(datas, cb) {
        if (datas == null || cb == null) {
            console.log("db_insert_plus need data and cb");
            return;
        }
        var mysqldb = new Sqldb();
        mysqldb.insert_plus(datas, function(ret,info){
            if (ret == 1) {
                cb(1,info);
            } else {
                if (info && info.sqlState == '23000' && info.code == 'ER_DUP_ENTRY' ) {
                    cb(-1, {errcode:-1,errmsg:'数据重复',errinfo:info});
                } else {
                    cb(-1, {errcode:-2,errmsg:'插入失败',errinfo:info});
                }                    
            }
        });
    }

    // 插入一条数据或者多条数据
    //obj ={
    //data: [] 或者{}
    //tablename:
    //cache:true false 是否缓存等到下一次存入
    //// 插入多条数据
    db_insert_batch(tablename, data, cb) {
        if (data == null || cb == null) {
            console.log("db_insert_batch obj or callback is null");
            return;
        }
        var mysqldb = new Sqldb();
        mysqldb.insert(tablename, data, function(ret,info){
            if (ret == 1) {
                cb(1,info);
            } else {
                if (info && info.sqlState == '23000' && info.code == 'ER_DUP_ENTRY' ) {
                    cb(-1, {errcode:-1,errmsg:'数据重复',errinfo:info});
                } else {
                    cb(-1, {errcode:-2,errmsg:'插入失败',errinfo:info});
                }                    
            }
        });
    }


    //注意obj不能是数组，只能是单个对象
    db_insert(obj, cb) {
        if (obj == null || obj.tablename == null || cb == null) {
            console.log("db_insert obj or tablename or cb is null");
            return;
        }
        var mysqldb = new Sqldb();
        //注意如果是存入cache，多次的字段值时要对应的
        if (obj.cache != null && obj.cache == true) {
            //var retobj = mysqlcache.get(obj.tablename);
            //if (retobj == null)//50秒之后自动插入
            //加入缓存
            mysqlcache.add(obj.tablename, obj.data, OUT_TIME_CONFIG, function (key, value) {
                //此处不需要回掉函数
                db_insert_batch(key, value, null);
            });
            cb(1, "add into cache,after ", OUT_TIME_CONFIG, " will auto inert!");
        }
        else {
            mysqldb.insert(obj.tablename, obj.data, function(ret,info){
                if (ret == 1) {
                    cb(1,info);
                } else {
                    if (info && info.sqlState == '23000' && info.code == 'ER_DUP_ENTRY' ) {
                        cb(-1, {errcode:-1,errmsg:'数据重复',errinfo:info});
                    } else {
                        cb(-1, {errcode:-2,errmsg:'插入失败',errinfo:info});
                    }                    
                }
            });
        }

    }

    // 更新一条数据
    db_update(obj, cb) {
        if (obj == null || obj.tablename == null || cb == null) {
            console.log("db_update obj some value is null");
            return;
        }
        var mysqldb = new Sqldb();

        mysqldb.querysql(false, obj);
        mysqldb.update(obj.tablename, obj.data, function(ret,info){
            if (ret == 1) {
                cb(1,info);
            } else {
                if (info && info.sqlState == '23000' && info.code == 'ER_DUP_ENTRY' ) {
                    cb(-1, {errcode:-1,errmsg:'数据重复',errinfo:info});
                } else {
                    cb(-1, {errcode:-2,errmsg:'更新失败',errinfo:info});
                }                    
            }
        });
    }
    // 创建表

    // 删除一条数据
    //obj:{
    //query 
    //f_in  //where field in
    //f_in_array
    //extra_where
    db_delete(obj, cb) {
        if (obj == null || obj.tablename == null || obj.query == null || cb == null) {
            console.log("db_delete obj some value is null");
            return;
        }
        var mysqldb = new Sqldb();
        mysqldb.querysql(false, obj);
        mysqldb.delete(obj.tablename, cb);
        //var db = mysql.create();
        //db.where(query);
        //db.delete(tablename, function (status, res) {
        //    cb(1, res);
        //    //暂时屏蔽
        //    //if(status == 1)
        //    //    oper_(tablename,query,cb);
        //    //else
        //    //    if(cb!=null)
        //    //        cb(-1,"notok");
        //})
    }



    /*
 //数据图表曲线
 port: 端口
 deviceid: 设备id,数组或者单个或者null
 time    : 基础时间
 timetype: 1 一天 2 一星期  3 一月 4 一年 5 二年 6 三年
 port    : 协议端口
 field   : 字段
 //port,deviceid, time, timetype, field
 */
    db_time_curve_data(obj, cb) {

        var fields = obj.field.split(',');
        if (obj.port == null)
            obj.port = 8051;
        var tablename = "log_" + obj.port;
        var interval = "";
        var condition = "";
        var unit = "";
        switch (parseInt(obj.timetype)) {
            case 1:
                interval = " 1 day ";
                condition = " HOUR(time) ";
                unit = "':00'";
                break;
            case 2:
                interval = " 1 week ";
                condition = " DAY(time) ";
                unit = "'号'";
                break;
            case 3:
                interval = " 1 month ";
                condition = " DAY(time) ";
                unit = "'号'";
                break;
            case 4:
                interval = " 1 year ";
                condition = " MONTH(time) ";
                unit = "'月'";
                break;
            case 5:
                interval = " 2 year ";
                condition = " MONTH(time) ";
                unit = "'月'";
                break;
            case 6:
                interval = " 3 year ";
                condition = "YEAR(time)";
                unit = "'年'";
                break;
            default:
                break;
        }
        var sql = "";
        var flen;
        if ((flen = obj.fields.length) > 1) {
            var tempfield = "";
            for (var i = 0; i < flen; i++) {
                tempfield += "avg(" + fields[i] + ") " + fields[i];
                if (i < flen - 1)
                    tempfield += ",";
            }
            sql = util.format(" select %s,%s time, %s as unit from %s where time between '%s' and date_add('%s', interval %s) group by %s",
                tempfield,
                condition,
                unit,
                obj.tablename,
                obj.time,
                obj.time,
                interval,
                condition
            )
        }
        else {
            sql = util.format(" select avg(%s) %s,%s time,%s as unit from %s where time between '%s' and date_add('%s', interval %s) group by %s",
                obj.field,
                obj.field,
                condition,
                unit,
                obj.tablename,
                obj.time,
                obj.time,
                interval,
                condition
            );
        }
        var mysqldb = new Sqldb();
        mysqldb.querys(sql, cb);


        //select avg(value1), DAY(time)  from log_8053 where time 
        //between '2018-01-01' and date_add('2018-01-01', interval  1 month ) group by
        //DAY(time)
        //select * from yk_datasub where time between '2017-12-24' and date_add('2017-12-24', interval 1 year)
        //select avg(point_num), MONTH(time) from yk_datasub where time between '2017-12-24' and date_add('2017-12-24', interval 1 year) GROUP BY MONTH(time)

    }

    //钱波 修改， 改成通用接口
    db_max(obj, cb) {
        if (obj.field == null || obj.tablename == null || cb == null) {
            console.log("error obj some value is null or cb is null");
        }
        var mysqldb = new Sqldb();
        var sql = util.format('select max(%s) from %s', obj.field, obj.tablename);
        mysqldb.querys(sql, null, cb);
    }
}


module.exports = new Db_modules;
//测试程序
//function callback_t(status, ret) {
//    console.log(status, ret[0].name);
//    console.log(status, ret[1]);
//}
//var sqls = "select name from project where id ='343';select name,clientname from user where id=1";
////execute_sql(2, sqls, callback_t);
//var mysqldb1 = new sqldb();
//mysqldb1.querys(sqls, null, callback_t);



